﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Diagnostics;
using System.ComponentModel;
using System.Text.RegularExpressions;

namespace SqlFileLayoutViewer {
    class DBAnalyzer {

        // This class will analyze database <DatabaseName> on instance <SQLServername>
        // and populate PageList and DBObjList with simplified representations
        // of all pages, and the names of all tables/indexes

        public DBAnalyzer(string SQLServerName, string DatabaseName) {

            // Compose connection string for the target DB 

            conString.DataSource = SQLServerName;
            conString.InitialCatalog = DatabaseName;
            conString.IntegratedSecurity = true;

            // Confirm that the intended database connection works
            testDBConnection();

        }

        public DBAnalyzer(string SQLServerName, string DatabaseName, string UserName, string Password) {

            // Compose connection string for the target DB using SQL Auth

            conString.DataSource = SQLServerName;
            conString.InitialCatalog = DatabaseName;
            conString.IntegratedSecurity = false;

            conString.UserID = UserName;
            conString.Password = Password;

            // Confirm that the intended database connection works
            testDBConnection();

        }

        private void testDBConnection() {

            // Validate that we can even connect to the DB
            using (SqlConnection scon = new SqlConnection(conString.ToString())) {

                try {
                    scon.Open();
                }
                catch (Exception ex) {
                    throw (new Exception(
                        String.Format("Failed to connect to the specified database {0} : {1}", conString.DataSource, conString.InitialCatalog)
                        , ex)
                    );
                }
                // Check for sysadmin rights
                SqlCommand testcmd = new SqlCommand("SELECT IS_SRVROLEMEMBER( 'sysadmin' );", scon);
                if ((int)testcmd.ExecuteScalar() != 1) {
                    throw (new Exception(
                        String.Format("Error: you must be a member of the sysadmin role to analyze {0} : {1}", conString.DataSource, conString.InitialCatalog)
                        )
                    );
                }
                scon.Close();
            }
        }

        public void runAnalysis() {

            // Scan the database and collect analysis for all pages

            // Collect metadata about the database such as the list of objects, the file sizes and total size
            populateDBObjList();
            populateDBFileList();
            ComputeTotalPages();

            // Scan the data pages in the database and collect per-page details
            populatePageDetails();

        }

        private void populateDBFileList() {

            // Get a list of files from the database

            string filesQuery = @"select file_id, size from sys.database_files where type = 0;";

            using (SqlConnection scon = new SqlConnection(conString.ToString())) {

                SqlCommand scom = new SqlCommand(filesQuery, scon);

                scon.Open();

                SqlDataReader rf = scom.ExecuteReader();
                while (rf.Read()) {
                    DBFileInfo dbf = new DBFileInfo();
                    dbf.FileID = Convert.ToInt32(rf["file_id"]);
                    dbf.NumPages = Convert.ToInt64(rf["size"]);
                    DBFileList.Add(dbf);
                }
                rf.Close();
                scon.Close();
            }
        }

        private void populateDBObjList() {

            // Get names of all the tables and indexes in the database
            // to supply the descriptions for what each DB page stores

            string ptnquery = @"select p.partition_id,
	                    s.name schemaname, 
	                    o.name objectname, 
	                    isnull(i.name, '') indexname, 
	                    p.partition_number partitionnumber
                    from sys.partitions p
	                    inner join sys.objects o on p.object_id = o.object_id
	                    inner join sys.schemas s on o.schema_id = s.schema_id
	                    inner join sys.indexes i on p.index_id = i.index_id and p.object_id = i.object_id;";

            using (SqlConnection scon = new SqlConnection(conString.ToString())) {

                SqlCommand scom = new SqlCommand(ptnquery, scon);
                scon.Open();

                SqlDataReader r = scom.ExecuteReader();
                while (r.Read()) {
                    DBObjInfo o = new DBObjInfo();
                    o.SchemaName = r["schemaname"].ToString();
                    o.ObjectName = r["objectname"].ToString();
                    o.IndexName = r["indexname"].ToString();
                    o.PartitionNo = Convert.ToInt32(r["partitionnumber"]);
                    _DBObjList.Add(Convert.ToInt64(r["partition_id"]), o);
                }
                r.Close();
                scon.Close();
            }
        }

        private void ComputeTotalPages() {

            // Add up the sizes of each file in the DBFileList
            foreach (DBFileInfo file in DBFileList) {
                TotalDBPages += file.NumPages;
            }

        }

        private void populatePageDetails() {

            // Make background workers to scan the database in parallel, asynchronously
            // using PageAnalyzer objects for ranges of data pages

            foreach (DBFileInfo dbf in DBFileList) {

                // Compute how may worker processes to use for each file - at least one per data file, perhaps more,
                // while trying to stay close to the total suggestedWorkers value
                // If there are few files, workers are assigned to data files in proportion to the relative sizes of the files

                int workersForThisFile = (int)Math.Ceiling(((decimal)dbf.NumPages / (decimal)TotalDBPages) * (decimal)suggestedWorkers);

                // Compute number of data pages to be scanned by each worker assigned this file
                Int64 pagesPerWorker = (Int64)Math.Ceiling((decimal)dbf.NumPages / (decimal)workersForThisFile);
                Int64 startPage = 0;
                Int64 endPage = -1;

                for (int i = 1; i <= workersForThisFile; i++) {

                    // Compute the page range for one worker to scan
                    startPage = endPage + 1;
                    endPage = startPage + pagesPerWorker;
                    if (endPage >= dbf.NumPages) {
                        endPage = dbf.NumPages - 1; // Note DB pages are zero-based
                    }
                    DBPageRange r = new DBPageRange(dbf.FileID, startPage, endPage);

                    // Create the worker
                    BackgroundWorkerOrdered w = new BackgroundWorkerOrdered(workers.Count());

                    w.WorkerSupportsCancellation = true;
                    w.WorkerReportsProgress = true;
                    w.DoWork += new DoWorkEventHandler(w_DoWork);
                    w.ProgressChanged += new ProgressChangedEventHandler(w_ProgressChanged);
                    w.RunWorkerCompleted += new RunWorkerCompletedEventHandler(w_RunWorkerCompleted);

                    // Start this worker chugging along:
                    w.RunWorkerAsync(r);

                    workers.Add(w);
                }
            }
        }


        private void w_DoWork(object sender, DoWorkEventArgs dwe) {

            // Get the range of pages to scan from the event args
            DBPageRange range = (DBPageRange)dwe.Argument;
            BackgroundWorkerOrdered worker = (BackgroundWorkerOrdered)sender;

            // Make a PageAnalyzer and a collection to accumulate the results
            PageAnalyzer pa = new PageAnalyzer(conString, range);
            List<PageInfo> partialPageList = new List<PageInfo>();

            Int64 pagesProcessed = 0;
            Int64 pagesToReport = 0;

            // Analyze the assigned range of pages
            // For the file assigned to this worker
            try {
                pa.open();

                while (pa.HasMore && !worker.CancellationPending) {

                    PageInfo pi = pa.read();
                    partialPageList.Add(pi);

                    pagesProcessed++;
                    pagesToReport++;

                    // Every x pages and at the end of the process, report progress
                    // PagesToReport should NOT be cumulative, gets reset each time

                    if (pagesToReport == 1000 || !pa.HasMore) {
                        Debug.WriteLine("Worker {0} reporting progress of {1} pages", worker.id, pagesToReport);

                        int prog = Convert.ToInt32((pagesProcessed / Convert.ToDouble(range.EndPage - range.StartPage)) * 100);

                        worker.ReportProgress(prog, pagesToReport);

                        //ProgressChangedEventArgs e = new ProgressChangedEventArgs(prog, pagesToReport);
                        //ProgressChanged(this, e);

                        pagesToReport = 0;

                    }
                }
            }
            catch (Exception e) {
                throw e;
            }
            finally {
                pa.close();
            }
            // Pass back the results accumulated for the data page range that this worker scanned
            dwe.Result = partialPageList;

        }

        void w_ProgressChanged(object sender, ProgressChangedEventArgs e) {
            // Debug.WriteLine("PA for worker {0} reported progress of {1} pages.", pa.Range.FragID, e.UserState);
            lock (progTracker) {
                progTracker.pagesRead += Convert.ToInt64(e.UserState);

                progTracker.percentComplete = (int)(((decimal)progTracker.pagesRead / (decimal)TotalDBPages) * 100);

                ProgressChanged(this, new ProgressChangedEventArgs(progTracker.percentComplete, progTracker.pagesRead));
            }
        }

        private void w_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs rwce) {

            BackgroundWorkerOrdered w = (BackgroundWorkerOrdered)sender;

            // Check the outcome of our worker

            if (rwce.Cancelled) {
                // Worker process was cancelled
                Debug.WriteLine("Worker {0} was canceled", w.id);
            }
            else if (rwce.Error != null) {
                // There were errors in the process
                throw new Exception(String.Format("Worker {0} encountered an error", w.id), rwce.Error);
            }
            else {
                // Completed successfully

                // Capture the output from the worker, but keep it in order with 
                // the results of the other workers, to preserve the overall order of 
                // the page information

                List<PageInfo> l = (List<PageInfo>)rwce.Result;

                lock (PageListFragments) {

                    PageListFragments.Add(w.id, l);

                    Debug.WriteLine("Worker {0} completed", w.id);

                    // Are *all* the workers done yet?
                    if (PageListFragments.Count() == workers.Count()) {

                        // Yes? Assemble the fragments created by each worker into one list

                        Debug.WriteLine("Parallel Scan Done");
                        lock (_PageList) {
                            for (int i = 0; i < PageListFragments.Count(); i++) {
                                _PageList.AddRange(PageListFragments[i]);
                            }
                        }

                        Debug.WriteLine("Page list assembled");

                        // Raise event indicating the database analysis is done
                        // and ready for use
                        AnalysisComplete(this, new EventArgs());
                    }
                }
            }
        }

        public string getDBCCPAGEOutput(int fileid, Int64 pageid) {

            SqlConnection scon = new SqlConnection(conString.ToString());

            // Configure parameterized SQL commands for DBCC Page

            SqlCommand TraceOnCmd = new SqlCommand("DBCC TRACEON (3604);", scon);

            SqlCommand DBCCPageCmd = new SqlCommand("DBCC PAGE (@DBName, @fileid, @pageid, @style);", scon);
            DBCCPageCmd.Parameters.Add(new SqlParameter("DBName", conString.InitialCatalog));
            DBCCPageCmd.Parameters.Add(new SqlParameter("fileid", 0));
            DBCCPageCmd.Parameters.Add(new SqlParameter("pageid", 0));
            DBCCPageCmd.Parameters.Add(new SqlParameter("style", 0));

            DBCCPageCmd.Parameters["fileid"].Value = fileid;
            DBCCPageCmd.Parameters["pageid"].Value = pageid;
            DBCCPageCmd.Parameters["style"].Value = 0;
            scon.Open();

            // Prep to run DBCC PAGE and direct its output to messages

            TraceOnCmd.ExecuteNonQuery();

            // Register event handler to receive/process sql info messages (the "message"
            // window content from SSMS, print statement output and errors)

            scon.InfoMessage += new SqlInfoMessageEventHandler(scon_InfoMessage); 

            // Run DBCC PAGE to populate this.PageInfoBuffer:
            DBCCPageCmd.ExecuteNonQuery();

            return PageInfoBuffer;
        }

        private void scon_InfoMessage(object sender, SqlInfoMessageEventArgs args) {
            // Store the output from the SQL query message area into a string   
            PageInfoBuffer = args.Message;
        }

        private string PageInfoBuffer;

        private SqlConnectionStringBuilder conString = new SqlConnectionStringBuilder();

        private Int64 TotalDBPages;

        private List<DBFileInfo> DBFileList = new List<DBFileInfo>();

        private class BackgroundWorkerOrdered : BackgroundWorker {

            // Class that allows the standard backgroundworker to be numbered with an id
            // so that we can order the results by worker
            public BackgroundWorkerOrdered(int id) {
                _id = id;
            }

            private int _id = 0;

            public int id {
                get {
                    return _id;
                }
            }
        }

        // Collections to store metadata about database objects and pages

        private Dictionary<Int64, DBObjInfo> _DBObjList = new Dictionary<Int64, DBObjInfo>();

        public Dictionary<Int64, DBObjInfo> DBObjList {
            get {
                return _DBObjList;
            }
        }

        private List<PageInfo> _PageList = new List<PageInfo>();

        public List<PageInfo> PageList {
            get {
                return _PageList;
            }
        }

        // A collection of BackgroundWorkers to read the target database in parallel

        private List<BackgroundWorker> workers = new List<BackgroundWorker>();

        // Sorted collection used to stage the results coming back from async worker
        // threads before assembling the complete set in _PageList

        private SortedList<int, List<PageInfo>> PageListFragments = new SortedList<int, List<PageInfo>>();

        // Default number of workers

        private int suggestedWorkers = 10;

        // Accumulator to report progress of the analysis

        private class ProgressTracker {
            public int percentComplete = 0;
            public Int64 pagesRead = 0;
        }

        private ProgressTracker progTracker = new ProgressTracker();

        // Convenience properties for SQL Server identification
        public string DatabaseName {
            get {
                return conString.InitialCatalog;
            }
        }

        public string SQLServerName {
            get {
                return conString.DataSource;
            }
        }

        public static bool IsFrag(PageInfo pi) {
            // For the given pageinfo compute whether it represents a fragment boundary
            if (pi.PageType == 0 || pi.NextPage == 0) return false;
            else return !(pi.NextFile == pi.FileID && pi.NextPage == pi.PageID + 1);
        }

        // For reporting progress on analysis to the calling form

        public event ProgressChangedEventHandler ProgressChanged;

        public event EventHandler AnalysisComplete;

    }

    public struct DBFileInfo {
        public int FileID;
        public Int64 NumPages;
    }

    public struct DBObjInfo {
        public string SchemaName;
        public string ObjectName;
        public string IndexName;
        public int PartitionNo;
    }

    public struct PageInfo {
        public int FileID;
        public Int64 PageID;
        public int PageType;
        public int PrevFile;
        public Int64 PrevPage;
        public int NextFile;
        public Int64 NextPage;
        public Int64 PartitionID;
        public int ObjectID;
        public int IndexID;
        public int FreeSpace;
        public int SlotCnt;
        public bool GAMAllocated;
        public bool SGAMAllocated;
        public override string ToString() {
            return String.Format("File ID: {0,5}, Page ID: {1,15}, Page Type: {2,2}, Prev File: {3,5}, Prev Page: {4,15}, Next File: {5,5}, Next Page {6,15}, Object: {6,10}, Index: {7,3}, Partition: {8,5}",
                FileID, PageID, PageType, PrevFile, PrevPage, NextFile, NextPage, ObjectID, IndexID, PartitionID);
        }
    }
}
